#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : JgSqldelete.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/1/21 9:31                 #
#                                                                   #
#                   @Last Update  : 2021/1/21 9:31                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于删除加工服务器上的Sql记录                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime

import pymysql

from settings import CJSQLCON, JGSQLCON, CJSQLTABLE, JGSQLTABLE


class Jgsqldelete:
    def __init__(self):
        self.cjsqlconn = pymysql.connect(host=CJSQLCON['HOST'], port=CJSQLCON['PORT'], db=CJSQLCON['DB'],
                                         user=CJSQLCON['USER'], passwd=CJSQLCON['PASSWD'], charset='utf8')
        self.jgsqlconn = pymysql.connect(host=JGSQLCON['HOST'], port=JGSQLCON['PORT'], db=JGSQLCON['DB'],
                                         user=JGSQLCON['USER'], passwd=JGSQLCON['PASSWD'], charset='utf8')

    def close(self):
        self.cjsqlconn.close()
        self.jgsqlconn.close()

    def sqldelete(self):
        """
        删除加工服务器上存在但是在采集服务器上不存在的Sql记录
        :return:True/erro
        """
        print(str(datetime.datetime.now()) + "：开始进行加工服务器的sql记录删除")
        cjurlcursor = self.cjsqlconn.cursor()       # 采集服务器的数据库游标
        jgurlcursor = self.jgsqlconn.cursor()        # 创建加工服务器游标
        for cjtable in CJSQLTABLE:
            jgtable = JGSQLTABLE[cjtable]
            cjsysid = [] # 采集服务器上Sql记录的sysid
            cjsql = '''SELECT sysid FROM %s ORDER BY SortA''' % (cjtable)
            try:
                cjurlcursor.execute(cjsql)
                cjresult = cjurlcursor.fetchall()
                for result in cjresult:
                    cjsysid.append(result[0])
            except Exception as e:
                print('%s：采集服务器查询错误:' % str(datetime.datetime.now()) + str(e))
            # jgsql = '''UPDATE `law` SET SYS_FLD_MARKSTATE = "0" where SYSID in (SELECT * FROM((select l.SYSID from law
            # as l LEFT JOIN taskrecord as t on l.SYSID = t.IndexingFileID where l.SYS_FLD_MARKSTATE = 1 and t.Type is null)AS a))'''
            # try:
            #     jgurlcursor.execute(jgsql)
            #     self.jgsqlconn.commit()
            # except Exception as e:
            #     print('加工服务器更新数据状态出错')
            jgsql = '''SELECT id,sysid,SortA FROM %s where SYS_FLD_MARKSTATE != "111"''' % (jgtable)
            try:
                jgurlcursor.execute(jgsql)
                jgresult = jgurlcursor.fetchall()
                for result in jgresult:
                    if result[1] not in cjsysid:
                        with open('jgclearsql.txt', 'a', encoding='utf-8') as f:
                            f.write(str(datetime.datetime.now()) + ":" + jgtable + ' ' + result[2] + ' ' + ' ' + str(result[0]) + ' ' + result[1] + '\n')
                        jgsql = '''DELETE FROM %s WHERE SYSID = '%s' AND (SYS_FLD_MARKSTATE = '0' OR SYS_FLD_MARKSTATE = '33' OR SYS_FLD_MARKSTATE = '666')''' % (jgtable, result[1])
                        try:
                            jgurlcursor.execute(jgsql)
                            self.jgsqlconn.commit()
                        except Exception as e:
                            print('%s：Sql删除加工服务器删除出错:' % str(datetime.datetime.now()) + str(e))
                        jgsql = '''UPDATE %s SET SYS_FLD_MARKSTATE = "111" WHERE SYSID = '%s' AND (SYS_FLD_MARKSTATE = '2' 
                        OR SYS_FLD_MARKSTATE = '4' OR SYS_FLD_MARKSTATE = '6' 
                        OR SYS_FLD_MARKSTATE = '999')''' % (jgtable, result[1])
                        try:
                            jgurlcursor.execute(jgsql)
                            self.jgsqlconn.commit()
                        except Exception as e:
                            print('%s：Sql数据修改状态加工服务器修改出错:' % str(datetime.datetime.now()) + str(e))
            except Exception as e:
                print('%s：Sql删除加工服务器查询出错:' % str(datetime.datetime.now()) + str(e))
        cjurlcursor.close()
        jgurlcursor.close()
        self.close()
        print(str(datetime.datetime.now()) + "：加工服务器的sql记录删除结束")
        return True
